How to implement a Sequential List in database/ Insert a record in the middle.

The Unofficial Newsletter of Delphi Users - by Mystic Software, Inc.

How to implement a Sequential List in database/ Insert a record in the middle.

{
Does anybody have an idea how to implement such a list in an elegant fashion?

Arthur Hoornweg
}

{
I had a similar problem with a list of delivery addresses that were delivered in a particular order. The best way is to have a field that allows you to insert a record between existing values.

=========================================================================================
A SQL database has no intrinsic order so inserting between two records has no meaning.
=========================================================================================

What we did was have a long integer that incremented by say 1000 for each record. When we wanted to insert between two records we just set this integer to a value between the records we inserted. Then everything is sorted by that integer and the list comes out the way you want it.

We also had a stored procedure that ran nightly and renumbered the field back to 1000 increments. Unless there are an awful lot of inserts this works well. If the range is not big enough then set it to 2000 or 5000 whatever.

Jeff Wright
}

Suppose a condition such that Primary key value once created should not be modified.

{

The best solution I have found to this is to use a floating point number for the primary key. When you need to insert a record between two existing records compute the value half way between the existing keys for the key of the new record.

Bill Todd - TeamB
}

Easy to implement but cannot inserting more number of records because of 15 digits restriction with float field type.

Here is the solution:

This function will get the fraction portion of a float Example: If the value is '10.1026', this function will return '0.000'.

function getDeciFrac(aForValue: Double): String;
var
   tmpStr: String;
   i, DeciPos: SmallInt;
begin
   tmpStr := FloatToStr(aForValue);
   DeciPos := Pos('.', tmpStr);

   ifDeciPos>0 then tmpStr := Copy(tmpStr, DeciPos+1, Length(tmpStr))
   else tmpStr := '';

   Result := '';
   for i:=1 to (Length(tmpStr)-1) do begin
     Result := Result + '0';
   end;

   Result := '0.'+Result;
end;

This function will compute a float value, which is in between first & second value. Example: If the value is '10.10 & 10.11', this function will return '10.101'.

{
Does someone know a function which calculates the neighbours of floating point numbers

Volker W. Walter
}

Yes! with little modification this function can be used as Next an Previous Floting point number.

function getInBewteenfFloat(FirstVal, SecondVal: Double): Double;
var
   DeciFracStr: String;
   tmpFloat: Double;
   runCount: SmallInt;
begin
   Result := 0;
   runCount := 1;

   if ((FirstVal=SecondVal) or
       ((FirstVal=0) and (SecondVal<=1))) then begin
     MessageDlg('Cannot be inserted !',mtError,[mbOK],0);
     Exit;
   end;

   if (SecondVal=0) then begin
     Result := Trunc(FirstVal)+1;
     Exit;
   end;

   if (FirstVal>0) then DeciFracStr := getDeciFrac(FirstVal)
   else DeciFracStr := getDeciFrac(SecondVal);

   while runCount<=15 do begin
     if(FirstVal>0) then begin
       tmpFloat := FirstVal + StrToFloat(DeciFracStr + '1');
     end else begin
       tmpFloat := SecondVal - StrToFloat(DeciFracStr + '1');
     end;

     DeciFracStr := DeciFracStr + '0';

{
You cannot compare floating point numbers (as Real) against an exact value. There are always errors due to loss of precision, caused by an limited number of binary digits (bits) used to represent a decimal value.
}

     tmpFloat := StrToFloat(FloatToStr(tmpFloat));

    if (tmpFloat>FirstVal) and(tmpFloat(*<)SecondVal) then begin
       Result := tmpFloat;
       Exit;
     end;

     inc(runCount);
   end;

   if(Length(FloatToStr(Result))>16) then Result := 0;

   if(Result<0) then begin
     Result := 0;
     MessageDlg('Cannot be inserted !',mtError,[mbOK],0);
   end;
end;

You have to create one float field as RecPosNo in the table where you want to insert record. Set RecPosNo as key field & also as first field along with the existing key fields. (Employee.db which is available in '\Program Files\Borland\ Delphi 5\Demos\Data' is used for the demo.)

Full source code of this project is attached as a FloatIns.Zip along with this article. Download & unzip this to a directory. Create an alias "FloatIns" of paradox type, Set path to \FloatIns\Data directory. Pl. go through BeforeInsert & AfterInsert event of QryEmployee in DataModule to know the use of above function. Compile & Run FloatIns. Use button Insert to Insert the record in-between & append to insert the record at the end.

Note: Do not allow user to enter/modify the values of RecPosNo or Primary key.
Even then if you want this to be entered by the user, necessary care has
to be taken to avoid duplicate records.

Feel free to e-mail me with questions, enhancement requests, and feedback.

This article is dedicated to the participants in the borland.public.delphi.database.desktop & objectpascal forum. Special thanks to Shaw for his proofing and suggestions. Finally thanks to Bill Tod, who made me to think in this direction.

*without parenthesis